package com.pur.zsms.pmsnmanage.user.dao.impl;

import java.util.ArrayList;
import java.util.List;

import javax.persistence.Query;

import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Repository;

import com.pur.zsms.common.dao.GenericDao;
import com.pur.zsms.common.dao.JpaTemplate;
import com.pur.zsms.common.exception.GenericException;
import com.pur.zsms.common.support.PageController;
import com.pur.zsms.common.support.PaginationSupport;
import com.pur.zsms.pmsnmanage.menu.po.MenuPo;
import com.pur.zsms.pmsnmanage.role.vo.RoleVo;
import com.pur.zsms.pmsnmanage.user.dao.IUserDao;
import com.pur.zsms.pmsnmanage.user.po.LdAddressPo;
import com.pur.zsms.pmsnmanage.user.po.UserLevelPo;
import com.pur.zsms.pmsnmanage.user.po.UserPo;
import com.pur.zsms.pmsnmanage.user.po.UserRolePo;
import com.pur.zsms.pmsnmanage.user.vo.UserVo;
import com.pur.zsms.shopmanage.po.ShopPo;
import com.pur.zsms.supplier.po.SupplierPo;
import com.pur.zsms.supplier.po.SupplierTypePo;
import com.pur.zsms.supplier.vo.SupplierVo;
import com.pur.zsms.systemsetting.datadictionary.po.CodeType;

@Repository("userDao")
public class UserDaoImpl extends GenericDao implements IUserDao {

	public PaginationSupport listUser(PageController pageController, UserVo userVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("select u.user_id userId,user_name userName,real_name realName,");
		ql.append("organ_id organName,email,is_active isActive,user_phone from t_user u where 1 =1 ");
		if (userVo != null) {
			if (StringUtils.isNotBlank(userVo.getUserName())) {
				ql.append(" and u.user_name like '%" + userVo.getUserName().trim() + "%'");
			}
			if (StringUtils.isNotBlank(userVo.getRealName())) {
				ql.append(" and u.real_name like '%" + userVo.getRealName().trim() + "%' ");
			}
			if (StringUtils.isNotBlank(userVo.getRoleName())) {
				ql.append(" and exists(select 1 from t_user_role ur where ");
				ql.append(" exists (select role_id from t_role where role_name like '%" + userVo.getRoleName().trim() + "%'");
				ql.append(" and role_id = ur.role_id)  and ur.user_id = u.user_id )");
			}
		}

		if (userVo.getSupId() != 0) {
			ql.append(" AND EXISTS ( SELECT 1 FROM t_supplier_user a WHERE a.`sup_id` = " + userVo.getSupId() + " AND u.user_id = a.`user_id` )");
		} else {
			ql.append(" AND NOT EXISTS (SELECT 1 FROM t_supplier s WHERE u.`user_id` = s.`user_id` )");
		}

		ql.append(" order by u.user_id desc");
		return this.findByNativeQuery(ql.toString(), pageController);
	}

	public PaginationSupport listUserForSupplierShop(PageController pageController, UserVo userVo) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("select u.user_id userId,user_name userName,real_name realName,");
		ql.append("organ_id organName,email,is_active isActive,user_phone from t_user u where 1 =1 ");
		if (userVo != null) {
			if (StringUtils.isNotBlank(userVo.getUserName())) {
				ql.append(" and u.user_name like '%" + userVo.getUserName().trim() + "%'");
			}
			if (StringUtils.isNotBlank(userVo.getRealName())) {
				ql.append(" and u.real_name like '%" + userVo.getRealName().trim() + "%' ");
			}

		}

		ql.append(" AND EXISTS ( SELECT 1 FROM t_supplier_shop_user a WHERE a.`sup_id` = " + userVo.getSupId() + " AND u.user_id = a.`user_id` )");

		ql.append(" order by u.user_id desc");
		return this.findByNativeQuery(ql.toString(), pageController);
	}

	public List<UserPo> findByUserName(String userName) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from UserPo o where o.userName=" + userName);
		List<UserPo> list = template.find(ql.toString(), null);
		return list;
	}

	public void addUser(UserPo c) throws GenericException {
		this.persist(c);
	}

	public void addUserRole(UserRolePo c) throws GenericException {
		this.persist(c);
	}

	public void addSupplierTypePo(SupplierTypePo c) throws GenericException {
		this.persist(c);
	}

	public void addUserLevel(UserLevelPo c) throws GenericException {
		this.persist(c);
	}

	public void updateUserLevel(UserLevelPo c) throws GenericException {
		this.merge(c);
	}

	public void updateUser(UserPo c) throws GenericException {
		UserPo oldUserPo = this.listUserByUserId(String.valueOf(c.getUserId()));
		c.setInsertTime(oldUserPo.getInsertTime());
		this.merge(c);
	}

	public void deleteUser(String userId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" delete UserPo o where o.userId=" + userId);
		template.executeUpdate(ql.toString());
	}

	public UserPo listUserByUserId(String userId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		UserPo c = new UserPo();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from UserPo o where o.userId=" + userId);
		List<UserPo> list = template.find(ql.toString(), null);
		if (list == null || list.size() == 0) {
			return null;
		} else {
			return list.get(0);
		}
	}

	public List<UserRolePo> listUserRoleByUserId(String userId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from UserRolePo o where o.userId=" + userId);
		List<UserRolePo> list = template.find(ql.toString(), null);
		return list;
	}

	public void updateUserRole(UserRolePo userRolePo) throws GenericException {
		this.merge(userRolePo);
	}

	public void deleteUserRole(String userRoleId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" delete UserRolePo o where o.userRoleId=" + userRoleId);
		template.executeUpdate(ql.toString());
	}

	public void deleteUserRoleByUserId(String userId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" delete UserRolePo o where o.userId=" + userId);
		template.executeUpdate(ql.toString());
	}

	public UserPo findUserLogon(String userName, String password) throws GenericException {
		UserPo user = null;
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o ");
		ql.append(" from UserPo o where (o.userName='" + userName + "' or o.userPhone ='" + userName + "' )");
		ql.append(" and o.password='" + password + "'");
		List<UserPo> list = template.find(ql.toString(), null);
		if (list.size() > 0) {
			user = list.get(0);
		}
		return user;
	}

	public List<MenuPo> getUserMenuList(String userId) throws GenericException {
		StringBuffer ql = new StringBuffer();
		ql.append("select * from t_menu pv");
		ql.append(" where pv.menu_id in (select rolepv.menu_id");
		ql.append(" from t_role_menu rolepv, t_user_role ur, t_role role");
		ql.append(" where ur.role_id = role.role_id");
		ql.append(" and ur.user_id = " + userId);
		ql.append("  and ur.role_id= rolepv.role_id");
		ql.append(" and role.is_active='Y')");
		ql.append(" order by pv.order");
		JpaTemplate template = this.getTemplate();
		Query query = this.entityManager.createNativeQuery(ql.toString(), MenuPo.class);
		List<MenuPo> list = query.getResultList();
		this.entityManager.close();
		// List<MenuPo> list = template.find(ql.toString(), null);

		return list;
	}

	@Override
	public List<UserPo> getAllUserPo() throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o from UserPo o ");
		List<UserPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<UserPo> getUserForSup(long supId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o from UserPo o WHERE o.userId IN(SELECT a.userId FROM SupplierUserPo a WHERE a.supId='" + supId + "') ");
		List<UserPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<UserLevelPo> getCompanyUser(UserPo lg) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o from UserLevelPo o where o.userId ='" + lg.getUserId() + "'");
		List<UserLevelPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<UserLevelPo> getUserLevelPoByUserId(long userId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o from UserLevelPo o where o.userId ='" + userId + "'");
		List<UserLevelPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<ShopPo> getUserPoListByUserIdString(String userIdString) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o from ShopPo o where o.shopId in(" + userIdString + ")");
		List<ShopPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<UserPo> getUserPoListByUserNameOrUserPhone(String userName, long userPhone) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o from UserPo o where 1=1 ");
		ql.append(" AND ( o.userName ='" + userName + "' OR o.userPhone = '" + userPhone + "')");
		List<UserPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<UserPo> getUserPoListByOpenId(String openId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o from UserPo o where o.openId ='" + openId + "'");
		List<UserPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<UserPo> getUserPoListByUserName(String userName) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o from UserPo o where o.userName ='" + userName + "'");
		List<UserPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<UserPo> getUserPoListByRealName(String userName) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o from UserPo o where o.realName ='" + userName + "'");
		List<UserPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<UserPo> getUserPoListByUserPhone(String phone) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o from UserPo o where o.userPhone ='" + phone + "'");
		List<UserPo> list = template.find(ql.toString(), null);
		return list;
	}

	@Override
	public List<UserPo> getAllCustUserPo() throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" SELECT p FROM UserPo p WHERE EXISTS (SELECT 1 FROM UserRolePo a, RolePo b WHERE a.userId = p.userId AND b.roleId = a.roleId AND b.roleName = '店长' )");
		List<UserPo> list = template.find(ql.toString(), null);
		return list;
	}

	// 店铺员工
	public List<UserPo> getUserForShop() throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" SELECT p FROM UserPo p WHERE NOT EXISTS (SELECT 1 FROM UserRolePo a, RolePo b WHERE a.userId = p.userId AND b.roleId = a.roleId AND b.roleName IN('供货商','供货商1')  )");
		List<UserPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<UserPo> userForSupplierShop(long supId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append("SELECT p FROM UserPo p WHERE EXISTS ( SELECT 1 FROM SupplierShopUserPo b WHERE p.userId =b.userId AND b.supId ='" + supId
				+ "' ) AND NOT EXISTS ( SELECT 1 FROM ShopUserPo a WHERE a.userId = p.userId ) ");
		List<UserPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<ShopPo> getShopForSupplier(int supId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" SELECT p FROM ShopPo p  ");
		List<ShopPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<Object> getSupType(long supId) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT GROUP_CONCAT( c. describ )FROM t_supplier_type s ,t_code c WHERE s. sup_id ='" + supId
				+ "' AND c. code_type_id ='mater_type' AND s. mater_type = c.code_oder ");
		List list = this.entityManager.createNativeQuery(sql.toString()).getResultList();
		return list;
	}

	public List<ShopPo> getShopForAdmin(long companyUser) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" SELECT s1 FROM ShopPo s1, ShopUserPo s2 WHERE s1.shopId = s2.shopId AND s2.userId IN (SELECT u1.userId FROM UserLevelPo u1 WHERE u1.levelId = 4 AND u1.companyUser = '"
				+ companyUser + "')  ");
		List<ShopPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<ShopPo> getShopForManager(long userId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" SELECT s1 FROM ShopPo s1, ShopUserPo s2 WHERE s1.shopId = s2.shopId AND s2.userId IN (SELECT u1.userId FROM UserLevelPo u1 WHERE u1.levelId = 4 AND u1.higherUser = '"
				+ userId + "')  ");
		List<ShopPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<ShopPo> shopForAccountant(long userId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" SELECT s1 FROM ShopPo s1, ShopUserPo s2 WHERE s1.shopId = s2.shopId AND s2.userId IN (SELECT u1.userId FROM UserLevelPo u1 WHERE u1.levelId = 4 AND u1.companyUser IN (SELECT u2.companyUser FROM UserLevelPo u2 WHERE u2.userId ='"
				+ userId + "'))  ");
		List<ShopPo> list = template.find(ql.toString(), null);
		return list;
	}

	// 店铺员工
	public List<UserPo> getUserForShopByShopId(long shopId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" SELECT u FROM UserPo u  WHERE EXISTS ( SELECT 1 FROM ShopUserPo su WHERE su.shopId='" + shopId + "'  AND su.userId = u.userId)");
		List<UserPo> list = template.find(ql.toString(), null);
		return list;
	}

	// 店铺员工and登记
	public List<UserPo> getUserForShopByShopIdAndLevel(long shopId, int level) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" SELECT u FROM UserPo u  WHERE EXISTS ( SELECT 1 FROM ShopUserPo su WHERE su.shopId='" + shopId + "'  AND su.userId = u.userId)");
		ql.append(" AND  u.userId IN (SELECT a.userId FROM UserLevelPo a WHERE a.levelId = " + level + ")");
		List<UserPo> list = template.find(ql.toString(), null);
		return list;
	}

	// 取得有订单的客户
	public List<ShopPo> getUserPoByOrderDateAndSupId(String orderDate, int supId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" SELECT p FROM ShopPo p WHERE ");
		ql.append(" EXISTS ( SELECT 1 FROM OrderSupplierPo s WHERE s.ordCustId = p.shopId AND s.ordDate = '" + orderDate + "' AND s.ordSupId ='"
				+ supId + "' )");
		List<ShopPo> list = template.find(ql.toString(), null);
		return list;
	}

	// 取得有订单的客户切没有周期定价方案
	public List<ShopPo> getUserPoByOrderDateAndSupIdForNotExistsCompanyPeriod(String orderDate, int supId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" SELECT p FROM ShopPo p WHERE ");
		ql.append(" EXISTS ( SELECT 1 FROM OrderSupplierPo s WHERE s.ordCustId = p.shopId AND s.ordDate = '" + orderDate + "' AND s.ordSupId ='"
				+ supId + "' )");
		ql.append(" AND NOT  EXISTS (SELECT 1 FROM QuotationCompanyPeriodPo qs WHERE qs.companyId =p.companyUser AND qs.quotationFlag =1 ) ");
		List<ShopPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<ShopPo> getShopPoByOrderDateAndSupIdForDistribute(String orderDate, int supId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" SELECT p FROM ShopPo p WHERE ");
		ql.append(" EXISTS ( SELECT 1 FROM OrderSupplierPo s WHERE s.ordCustId = p.shopId AND s.ordDate = '" + orderDate + "' AND s.ordSupId ='"
				+ supId + "' )");
		ql.append(" AND NOT EXISTS ( SELECT 1 FROM SupplierDistributePo d  WHERE d.custId = p.shopId AND d.supId = '" + supId + "' AND d.disDate = '"
				+ orderDate + "' )");
		List<ShopPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<ShopPo> getUserPoByOrderDateAndUserId(String orderDate, String userId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" SELECT p FROM ShopPo p WHERE p.shopId = '" + userId
				+ "' AND EXISTS ( SELECT 1 FROM OrderSupplierPo s WHERE s.ordCustId = p.shopId AND s.ordDate = '" + orderDate + "')");
		List<ShopPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<UserPo> getAllUserPoBylevelOrProvinceAndCityAndCountry(UserVo userVo) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o from UserPo o,UserLevelPo a where a.userId=o.userId ");

		if (userVo.getUserlevel() == 0) {
			ql.append(" and a.levelId <='3' ");
		} else {
			ql.append(" and a.levelId ='" + userVo.getUserlevel() + "' ");
		}
		if (userVo.getProvinceShow() != 0) {
			ql.append(" and a.province ='" + userVo.getProvinceShow() + "' ");
		}
		if (userVo.getCityShow() != 0) {
			ql.append(" and a.city ='" + userVo.getCityShow() + "' ");
		}
		if (userVo.getCountryShow() != 0) {
			ql.append(" and a.country ='" + userVo.getCountryShow() + "' ");
		}
		if (userVo.getLevelIdcopy() != 0) {
			ql.append(" and a.levelId < '" + userVo.getLevelIdcopy() + "'");
		}

		if (userVo.getSupId() != 0) {
			ql.append(" AND EXISTS ( SELECT 1 FROM SupplierUserPo b WHERE b.userId = a.userId AND b.supId=" + userVo.getSupId() + ")");
		}

		if (userVo.getCompanyUser() != 0) {
			ql.append(" and a.companyUser = (SELECT b.companyId FROM CompanyPo b WHERE b.companyId = " + userVo.getCompanyUser() + ")");
		}
		List<UserPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<UserPo> getUserForSupplierCompany(SupplierVo c) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o from UserPo where 1=1 ");
		//
		ql.append(" AND EXISTS (SELECT 1 FROM UserLevelPo c WHERE c.userId=o.userId AND c.companyUser='" + c.getCompanyId() + "')");
		ql.append(" AND NOT EXISTS (SELECT 1 FROM SupplierUserPo a WHERE a.userId=o.userId AND a.supId IN(SELECT b.supplierId FROM SupplierPo b WHERE b.companyId='"
				+ c.getCompanyId() + "')  )");
		List<UserPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<UserPo> getAllUserPoBylevelOrProvinceAndCityAndCountryForcheck(UserVo userVo) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o from UserPo o,UserLevelPo a where a.userId=o.userId ");

		ql.append(" and a.levelId ='4' ");

		if (userVo.getProvinceShow() != 0) {
			ql.append(" and a.province ='" + userVo.getProvinceShow() + "' ");
		}
		if (userVo.getCityShow() != 0) {
			ql.append(" and a.city ='" + userVo.getCityShow() + "' ");
		}
		if (userVo.getCountryShow() != 0) {
			ql.append(" and a.country ='" + userVo.getCountryShow() + "' ");
		}
		List<UserPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<UserPo> getAllUserPoForCust() throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o from UserPo o WHERE EXISTS  (SELECT 1 FROM RolePo r,UserRolePo ur WHERE o.userId = ur.userId  AND  r.roleId = ur.roleId AND r.roleName ='店长')  ");
		List<UserPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<ShopPo> getStoreCustUserPoForCust() throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o from ShopPo o WHERE NOT EXISTS ( SELECT 1 FROM  StorePo s WHERE s.storeCustId = o.shopId) ");
		List<ShopPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<LdAddressPo> getProvince() throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o from LdAddressPo o where o.placetype = '1'");
		List<LdAddressPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<LdAddressPo> getCity(int parentId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o from LdAddressPo o where o.placetype = '2' ");
		if (parentId != 0) {
			ql.append("and  o.upplacename = '" + parentId + "'");
		}
		List<LdAddressPo> list = template.find(ql.toString(), null);
		return list;
	}

	public List<LdAddressPo> getCountry(int parentId) throws GenericException {
		JpaTemplate template = this.getTemplate();
		StringBuffer ql = new StringBuffer();
		ql.append(" select o from LdAddressPo o where o.placetype = '3' ");
		if (parentId != 0) {
			ql.append("and  o.upplacename = '" + parentId + "'");
		}
		List<LdAddressPo> list = template.find(ql.toString(), null);
		return list;
	}

}
